package com.sds.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.sds.util.Util_DB;
import com.sds.vo.LineVO;

public class LineDAO {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	public void insertLine(LineVO line) {

		try {
			Util_DB.connection();
			if (conn == null) {
				System.out.println("Connection Error - [ Location : Line ]");
			} else {
				pstmt = conn
						.prepareStatement("insert into t_line values(?, ?, ?, ?)");
				pstmt.setString(1, line.getLine_id());
				pstmt.setString(2, line.getLine_depart());
				pstmt.setString(3, line.getLine_arrive());
				pstmt.setString(4, line.getLine_price());
				pstmt.executeUpdate();
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}

	public LineVO selectLine(String line_id) {
		LineVO line = null;
		try {
			Util_DB.connection();
			if (conn == null) {
				System.out.println("Connection Error - [ Location : Line ]");
			} else {
				pstmt = conn
						.prepareStatement("select * from t_line where line_id = ?");

				pstmt.setString(1, line_id);

				ResultSet rs = pstmt.executeQuery();

				while (rs.next()) {
					line = new LineVO(rs.getString("line_id"),
							rs.getString("line_depart"),
							rs.getString("line_arrive"),
							rs.getString("line_price"));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(rs, pstmt, conn);
		}
		return line;
	}

	public ArrayList<LineVO> selectLineList() {
		ArrayList<LineVO> lineList = new ArrayList<LineVO>();
		try {
			Util_DB.connection();
			if (conn == null) {
				System.out.println("Connection Error - [ Location : Line ]");
			} else {
				pstmt = conn
						.prepareStatement("select * from t_line order by line_id");

				ResultSet rs = pstmt.executeQuery();

				while (rs.next()) {
					lineList.add(new LineVO(rs.getString("line_id"), rs
							.getString("line_depart"), rs
							.getString("line_arrive"), rs
							.getString("line_price")));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(rs, pstmt, conn);
		}
		return lineList;
	}

	public void updateLine(LineVO line) {
		try {
			Util_DB.connection();
			if (conn == null) {
				System.out.println("Connection Error - [ Location : Line ]");
			} else {
				pstmt = conn
						.prepareStatement("update t_line set line_depart = ?, line_arrive = ?, line_price = ? where line_id = ?");

				pstmt.setString(1, line.getLine_depart());
				pstmt.setString(2, line.getLine_arrive());
				pstmt.setString(3, line.getLine_price());
				pstmt.setString(4, line.getLine_id());
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}

	public void deleteLine(String line_id) {
		try {
			Util_DB.connection();
			if (conn == null) {
				System.out.println("Connection Error - [ Location : Line ]");
			} else {
				pstmt = conn
						.prepareStatement("delete t_line where line_id = ?");
				pstmt.setString(1, line_id);
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			Util_DB.disconnection(null, pstmt, conn);
		}
	}
}
